package Course;

import javax.swing.text.html.HTMLDocument;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

public class CourseDao {
      //获取数据库连接对象
      public Connection getCon(){
          Connection con=null;
          try {
              Class.forName("com.mysql.cj.jdbc.Driver");
              String url="jdbc:mysql://localhost:3306/jwglxt?serverTimezone=UTC&characterEncoding=utf-8";
              con= DriverManager.getConnection(url,"root","root");
          }catch (Exception e){
              e.printStackTrace();
          }
          return con;
      }
      //新增课程
      public int addCourse(String courseNo,String courseName,float credit,String classRoom,int classHour){
          Connection con=this.getCon();
          try {
              //sql语句
              String sql="insert into tb_course (courseNo,courseName,credit,classRoom,classHour) values (?,?,?,?,?)";
              //创建ps对象
              PreparedStatement ps=con.prepareStatement(sql);
              ps.setString(1,courseNo);
              ps.setString(2,courseName);
              ps.setFloat(3,credit);
              ps.setString(4,classRoom);
              ps.setInt(5,classHour);
              //执行sql语句
              int row=ps.executeUpdate();
              ps.close();
              con.close();
              return row;
          }catch (Exception e){
              e.printStackTrace();
              return 0;
          }
      }
      //依据课程号查找课程
      public CourseEnity getCourse(String courseNo){
          Connection con=this.getCon();
          try {
              //sql语句
              String sql="select courseNo,courseName,credit,classRoom,classHour from tb_course where courseNo=?";
              //创建preparStatement
              PreparedStatement ps=con.prepareStatement(sql);
              ps.setString(1,courseNo);
              //执行
              ResultSet rs =ps.executeQuery();
              CourseEnity courseEnity=new CourseEnity();
              while (rs.next()){
                  courseEnity.setCourseNo(rs.getString("courseNo"));
                  courseEnity.setCourseName(rs.getString("courseName"));
                  courseEnity.setCredit(rs.getFloat("credit"));
                  courseEnity.setClassRoom(rs.getString("classRoom"));
                  courseEnity.setClassHour(rs.getInt("classHour"));
              }
              rs.close();
              ps.close();
              con.close();
              return courseEnity;
          }catch (Exception e){
              e.printStackTrace();
              return null;
          }
      }
      //修改课程
    public int updateCourse(String courseNo,String courseName,float credit,String classRoom,int classHour){
          Connection con=this.getCon();
          try {
              //sql语句
              String sql="update tb_course set courseName=?,credit=?,classRoom=?,classHour=? where courseNo=?";
              //创建ps对象
              PreparedStatement ps=con.prepareStatement(sql);
              ps.setString(1,courseName);
              ps.setFloat(2,credit);
              ps.setString(3,classRoom);
              ps.setInt(4,classHour);
              ps.setString(5,courseNo);
              //执行sql
              int row=ps.executeUpdate();
              ps.close();
              con.close();
              return row;
          }catch (Exception e ){
              e.printStackTrace();
              return 0;
          }
    }
    //删除课程
    public int deleteCourse(String courseNo){
          Connection con=this.getCon();
          try {
              //sql语句
              String sql="delete from tb_course where courseNo=?";
              PreparedStatement ps=con.prepareStatement(sql);
              ps.setString(1,courseNo);
              int row=ps.executeUpdate();
              ps.close();
              con.close();
              return row;
          }catch (Exception e){
              e.printStackTrace();
              return 0;
          }
    }

    //查询课程号
    public ArrayList<String> getCourseNo(){
        Connection con=this.getCon();
        String sql="select courseNo from tb_course";
        ArrayList<String> lists=new ArrayList<>();
        try {
            PreparedStatement ps=con.prepareStatement(sql);
            ResultSet rs=ps.executeQuery();
            while (rs.next()){
                String CourseNo=rs.getString("CourseNo");
                lists.add(CourseNo);
            }
            return lists;
        }catch (Exception e){
            e.printStackTrace();
            return null;
        }
    }
}
